package ch.zbw.pra.sysventory.domain.dbhandling;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import ch.zbw.pra.sysventory.domain.dbconnector.IDBConnector;
import ch.zbw.pra.sysventory.domain.model.Computer;
import ch.zbw.pra.sysventory.domain.model.Nic;
import ch.zbw.pra.sysventory.domain.model.OperatingSystem;
import ch.zbw.pra.sysventory.domain.model.Printer;
import ch.zbw.pra.sysventory.domain.model.Processor;
import ch.zbw.pra.sysventory.domain.model.Scanversion;
import ch.zbw.pra.sysventory.domain.model.Software;
import ch.zbw.pra.sysventory.domain.model.VendorSoftware;
import ch.zbw.pra.sysventory.domain.util.MyLogger;

public class GetAllScanversionsFromDB {

	private static final org.slf4j.Logger log = org.slf4j.LoggerFactory
			.getLogger(GetAllScanversionsFromDB.class);

	private List<Scanversion> scanversions;

	private IDBConnector dbconnector;

	public GetAllScanversionsFromDB(IDBConnector dbconnector) {
		this.dbconnector = dbconnector;
	}

	public List<Scanversion> getAllScanversions() {
		this.scanversions = new ArrayList<Scanversion>();
		String query = "SELECT * FROM scanversion";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				int idScanversion = rs.getInt("idScanversion");
				int numberOfScanversion = rs.getInt("numberOfScanversion");
				Timestamp timestampScanversion = rs.getTimestamp("timestampScanversion");
				Computer computer = selectComputer(idScanversion);
				Scanversion scanversion = new Scanversion(numberOfScanversion,
						timestampScanversion, computer);
				this.scanversions.add(scanversion);
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
			this.dbconnector.rollback();
		}
		return this.scanversions;
	}

	private Computer selectComputer(int idScanversion) {
		Computer computer = new Computer();
		int idComputer = 0;
		String hostNameComputer = "";
		String sidComputer = "";
		String ramComputer = "";
		String typeComputer = "";
		int idOperatingSystem = 0;
		OperatingSystem os = null;
		Processor processor = null;
		List<Software> softwares = null;
		List<Printer> printers = null;
		List<Nic> nics = null;

		String query = "SELECT c.idComputer, c.hostNameComputer, c.sidComputer, c.ramComputer, c.typeComputer, c.operatingSystem_idOperatingSystem "
				+ "FROM scanversion_has_computer shc "
				+ "JOIN computer c ON shc.computer_idComputer = c.idComputer "
				+ "WHERE shc.scanversion_idScanversion = " + idScanversion;
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				idComputer = rs.getInt("idComputer");
				hostNameComputer = rs.getString("hostNameComputer");
				sidComputer = rs.getString("sidComputer");
				ramComputer = rs.getString("ramComputer");
				typeComputer = rs.getString("typeComputer");
				idOperatingSystem = rs.getInt("operatingSystem_idOperatingSystem");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		os = selectOperatingSystem(idOperatingSystem);
		processor = selectProcessor(idComputer);
		printers = selectPrinters(idComputer);
		nics = selectNics(idComputer);
		softwares = selectSoftwares(idComputer);
		computer.setHostNameComputer(hostNameComputer);
		computer.setSidComputer(sidComputer);
		computer.setRamComputer(ramComputer);
		computer.setTypeComputer(typeComputer);
		computer.setOs(os);
		computer.setPrinters(printers);
		computer.setNics(nics);
		computer.setSoftwares(softwares);
		computer.setProcessor(processor);
		return computer;
	}

	private List<Nic> selectNics(int idComputer) {
		List<Nic> nics = new ArrayList<Nic>();
		String query = "SELECT n.nameNic, n.ipv4Nic, n.ipv6Nic, n.macAdressNic, n.standardGatewayNic, n.dhcpNic, n.subnetNic "
				+ "FROM computer_has_nic chn "
				+ "JOIN nic n ON chn.nic_idNic = n.idNic "
				+ "WHERE chn.computer_idComputer = "
				+ idComputer
				+ " ORDER BY n.standardGatewayNic DESC";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				String nameNic = rs.getString("nameNic");
				String ipv4Nic = rs.getString("ipv4Nic");
				String ipv6Nic = rs.getString("ipv6Nic");
				String macAdressNic = rs.getString("macAdressNic");
				String standardGatewayNic = rs.getString("standardGatewayNic");
				String dhcpNic = rs.getString("dhcpNic");
				String subnetNic = rs.getString("subnetNic");
				Nic nic = new Nic(nameNic, ipv4Nic, ipv6Nic, macAdressNic, standardGatewayNic,
						dhcpNic, subnetNic);
				nics.add(nic);
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return nics;
	}

	private List<Software> selectSoftwares(int idComputer) {
		List<Software> softwares = new ArrayList<Software>();
		String query = "SELECT s.idSoftware, s.nameSoftware, s.versionSoftware "
				+ "FROM computer_has_software chs "
				+ "JOIN software s ON chs.software_idSoftware = s.idSoftware "
				+ "WHERE chs.computer_idComputer =  " + idComputer + " ORDER BY s.nameSoftware";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				int idSoftware = rs.getInt("idSoftware");
				String nameSoftware = rs.getString("nameSoftware");
				String versionSoftware = rs.getString("versionSoftware");
				VendorSoftware vendor = selectVendor(idSoftware);
				Software software = new Software(nameSoftware, versionSoftware, vendor);
				softwares.add(software);
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return softwares;
	}

	private VendorSoftware selectVendor(int idSoftware) {
		VendorSoftware vendor = new VendorSoftware();
		String query = "SELECT vs.nameVendorSoftware F"
				+ "ROM software_has_vendorSoftware shv "
				+ "JOIN vendorSoftware vs ON shv.vendorSoftware_idVendorSoftware = vs.idVendorSoftware "
				+ "WHERE shv.software_idSoftware = " + idSoftware;
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				String nameVendorSoftware = rs.getString("nameVendorSoftware");
				vendor.setNameVendorSoftware(nameVendorSoftware);
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vendor;
	}

	private Processor selectProcessor(int idComputer) {
		Processor processor = new Processor();
		String nameProcessor = "";
		String familyProcessor = "";
		int coresProcessor = 0;
		int clockProcessor = 0;
		String query = "SELECT p.nameProcessor, p.familyProcessor, p.coresProcessor, p.clockProcessor "
				+ "FROM computer_has_processor chp "
				+ "JOIN processor p ON chp.processor_idProcessor = p.idProcessor "
				+ "WHERE chp.computer_IdComputer = " + idComputer;
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				nameProcessor = rs.getString("nameProcessor");
				familyProcessor = rs.getString("familyProcessor");
				coresProcessor = rs.getInt("coresProcessor");
				clockProcessor = rs.getInt("clockProcessor");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		processor.setNameProcessor(nameProcessor);
		processor.setFamilyProcessor(familyProcessor);
		processor.setCoresProcessor(coresProcessor);
		processor.setClockProcessor(clockProcessor);
		return processor;
	}

	private List<Printer> selectPrinters(int idComputer) {
		List<Printer> printers = new ArrayList<Printer>();
		String query = "SELECT p.namePrinter, p.driverNamePrinter, p.driverVersionPrinter "
				+ "FROM computer_has_printer chp "
				+ "JOIN printer p ON chp.printer_idPrinter = p.idPrinter "
				+ "WHERE chp.computer_idComputer = " + idComputer + " ORDER BY p.namePrinter";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				String namePrinter = rs.getString("namePrinter");
				String driverNamePrinter = rs.getString("driverNamePrinter");
				String driverVersionPrinter = rs.getString("driverVersionPrinter");
				Printer printer = new Printer(namePrinter, driverNamePrinter, driverVersionPrinter);
				printers.add(printer);
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return printers;
	}

	private OperatingSystem selectOperatingSystem(int idOperatingSystem) {
		OperatingSystem os = new OperatingSystem();
		String query = "SELECT os.nameOperatingSystem, os.buildOperatingSystem, os.architecture32Bit64BitOperatingSystem "
				+ "FROM operatingSystem os WHERE os.idOperatingSystem = " + idOperatingSystem;
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				String nameOperatingSystem = rs.getString("nameOperatingSystem");
				String buildOperatingSystem = rs.getString("buildOperatingSystem");
				String architecture32Bit64BitOperatingSystem = rs
						.getString("architecture32Bit64BitOperatingSystem");
				os.setNameOperatingSystem(nameOperatingSystem);
				os.setBuildOperatingSystem(buildOperatingSystem);
				os.setArchitecture32Bit64BitOperatingSystem(architecture32Bit64BitOperatingSystem);
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return os;
	}
}
